﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using Citi.PTSConsolidation.Domain;
using Citi.PTSConsolidation.Domain.Utils;
using Citi.RoutineBase;

namespace Citi.PTSConsolidation.SchedulerUI
{
    public class TimeBookingImport : IExecutableRoutine
    {
        private string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }
        private string ExcelConnectionString { get { return ConfigurationManager.AppSettings["ExcelConnectionString"]; } }
        private string TimeBookingExcelPath { get { return ConfigurationManager.AppSettings["TimeBookingExcelPath"]; } }

        public IEnumerable<RoutineParameter> Parameters
        {
            get
            {
                return new List<RoutineParameter>
                {
                    new RoutineParameter { Description = "Month(s) in report. Separated by comma (,)", Name = "Months", Nullable = false, Required = true, Type = typeof(string) },
                    new RoutineParameter { Description = "Year in report", Name = "Year", Nullable = false, Required = true, Type = typeof(int) }
                };
            }
        }

        public void Rollback(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            currentAction.Description = "Verifying program without Business Relationship";
            var query = ProjectProgramTemp.GetQueryProgramWithoutOwner();
            currentAction.Description = query;
            var programs = SqlHelper.ExecuteReader(query, PtsConnectionString);

            if (programs.Rows.Count > 0)
            {
                currentAction.Description = "There're programs without Business Relationship";
                throw new Exception("There're programs without Business Relationship");
            }

            var importedPath = Path.Combine(TimeBookingExcelPath, "Imported");

            if (!Directory.Exists(importedPath))
                Directory.CreateDirectory(importedPath);

            currentAction.Description = string.Format("Looking for file in {0}", TimeBookingExcelPath);
            var files = Directory.GetFiles(TimeBookingExcelPath).Where(x => x.EndsWith(".xlsx"));

            if (!files.Any())
            {
                currentAction.Description = "No files were found.";
                throw new Exception("No files were found.");
            }

            var months = parameters["Months"].ToString();
            var year = int.Parse(parameters["Year"].ToString());
            DeletePreviousData(currentAction, months, year);

            foreach (var file in files)
            {
                currentAction.Description = string.Format("{0} found", Path.GetFileName(file));
                currentAction.Description = "Copying data";
                SqlHelper.CopyExcelToSql(string.Format(ExcelConnectionString, file), TimeBooking.QueryExcel,
                    "TimeBooking", PtsConnectionString, TimeBooking.ExcelColumnMappings, "Report Detailed", true);

                currentAction.Description = "Moving file to import folder";
                var importedFile = Path.Combine(importedPath, Path.GetFileName(file));

                if (File.Exists(importedFile)) File.Delete(importedFile);
                File.Move(file, importedFile);
            }
        }

        private void DeletePreviousData(ICurrentAction currentAction, string months, int year)
        {
            currentAction.Description = "Deleting Previous Data";
            var query = string.Format(TimeBooking.QueryDeletePrevious, months, year);
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        public void Stop(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }
    }
}
